% cohort method to statistics on duration-year data
clear all;
clc;
freqj=4;% 4 quarters
year_list=1998:2010;
quarter_list=1:5;

DEFAULT = -2;
DURATION = 1;
NUMBER_OF_YEAR = length(year_list) - DURATION;

% year_list_text=num2str((year_list)');
% quarter_list_text=num2str((quarter_list)');

rateList= 1.5:0.5:4.5;
rateList=[rateList, DEFAULT];
RATE_LIST_LENGTH = length(rateList);
Quarter_LIST_LENGTH = length(quarter_list)-1;
db1_open = database('db2_fin','', ''); %open the connection to the database
% table ='TP06_Q_RJ_fin_R5_G1000k_MR7';
% table = 'TP06_Q_RJ_fin_R5_G250_1000_MR7';
% table ='TP06_Q_RJ_fin_R5_G50_250_MR7';
table ='TP06_Q_RJ_fin_R5_G50_MR7';

durationCount = zeros(NUMBER_OF_YEAR,Quarter_LIST_LENGTH, RATE_LIST_LENGTH -1,RATE_LIST_LENGTH);

for yearIdx=1:NUMBER_OF_YEAR % i=1:11 number of year
    for quarterIdx = 1:Quarter_LIST_LENGTH
        
        year1 = year_list(yearIdx);
        year2 = year1 + floor(quarter_list(quarterIdx) / 4);
        quarter_lookfor=quarter_list(quarterIdx:quarterIdx+DURATION);
        quarter_lookfor_length = length(quarter_lookfor);
        
        quarter1 = quarter_list(quarterIdx);
        quarter2 = mod(quarter_list(quarterIdx), 4) + DURATION;
        
        %get data for 2 quarters (Q4 and Q1)
        sqlquery1 = strcat(['SELECT Year, quarter, [Customer Number] as custumerId, [Overall Risk Rating (Hist)] as riskRating, status, default FROM ' table ' where (year = ''' num2str(year1) ''' And quarter = ' num2str(quarter1) ' ) or (year = ''' num2str(year2) ''' And quarter = ' num2str(quarter2) ' )']);
        
        setdbprefs('DataReturnFormat','structure')
        %         disp(sqlquery1);
        results = fetch(db1_open, sqlquery1);
        
        val = size(results.quarter);
        
        custumersMap = containers.Map();
        % ---------------------------------------------------------------------
        % FILL RATE
        
        for i =1:val
            
            custumerIdStr = results.custumerId{i};
            riskRating = results.riskRating(i);
            quarter = results.quarter(i);
            year = results.Year(i);
            year = str2double(year);
            
            default = strcmp('Default', results.default(i));
            unhealthy = strcmp('Unhealthy', results.status(i));
            
            if isKey(custumersMap, custumerIdStr) == 0
                %the custumer is not in the map, so create it
                
                %create structure
                myStruct.custumerId = custumerIdStr;
                %set the rates to default
                myStruct.rates = -1 * ones (1, quarter_lookfor_length); %[-1, -1];
            else
                myStruct = custumersMap(custumerIdStr);
            end
            
            % put the right rating
            %             if quarterIdx == Quarter_LIST_LENGTH
            %                 myStruct = fillRateQ4(myStruct, quarter, riskRating, default, unhealthy);
            %             else
            
            
            
            quarter = quarter + ((year - year1) * 4);
            
            
            myStruct = fillRateQ(myStruct, quarter_lookfor, quarter, riskRating, default, unhealthy);
            %            end
            
            % insert data in the map
            custumersMap(custumerIdStr) = myStruct;
        end
        
        
        
        
        
        
        % ---------------------------------------------------------------------
        % FILTRE
        % -1 == disapear or don't exist
        % -2 == default
        % 0  == i don't know
        
        custumersStructures = custumersMap.values;
        custumersStructuresLength = length(custumersStructures);
        
        
        
        for i = 1:custumersStructuresLength
            myStruct = custumersStructures{i};
            
            rate = myStruct.rates(1);
            if rate == -1 || rate == 0
                remove(custumersMap, myStruct.custumerId);
            else
                for j = 2 : quarter_lookfor_length
                    rate = myStruct.rates(j);
                    if rate == DEFAULT
                        myStruct.rates(quarter_lookfor_length) = DEFAULT;
                    end
                end
            end
        end
        
        
        % ---------------------------------------------------------------------
        % COUNT
        
        custumersStructures = custumersMap.values;
        custumersStructuresLength = length(custumersStructures);
        
        %quarter_lookfor_length = length(quarter_lookfor);
        
        
        
        
        for i = 1 : custumersStructuresLength
            myStruct = custumersStructures{i};
            
            rate = myStruct.rates(1);
            position1 = find(rateList == rate);
            
            rate = myStruct.rates(quarter_lookfor_length);
            position2 = find(rateList == rate);
            
            
            %get the count for one path
            pCount = durationCount(yearIdx, quarterIdx, position1, position2);
            
            %increment the count + 1 for that path
            durationCount(yearIdx,quarterIdx, position1,position2) = pCount + 1;
        end
        
        disp( {'Year: ' year1  ' Quarter: ' quarter1 ' to  ' quarter2});
        
    end
end
close (db1_open);

%To calculate the average counts and prob

% PathCountTotal=sum(durationCount(1:NUMBER_OF_YEAR,:,:));
% PathCountAvg=PathCountTotal./NUMBER_OF_YEAR;
% durationProb=zeros(1,RATE_LIST_LENGTH -1,RATE_LIST_LENGTH);
%
% for y=1:NUMBER_OF_YEAR
%     s = sum(durationCount(y, :, :),3);
%     for i=1:RATE_LIST_LENGTH
%         durationProb(y, :, i)= durationCount(y, :, i)./s;
%     end
% end
% 
% filename='TP06_Q_RJ_fin_R5_G1000k_MR7.xls';
% year_list=[1998:2009]';
% year_list_text=num2str(year_list);
% quarter_list=[1:4]';
% quarter_list_text=num2str(quarter_list);
% 
% for i=1:NUMBER_OF_YEAR
%     for j=1:Quarter_LIST_LENGTH
%         xx(:,:)=durationCount(i,j,:,:);
%         sheetlist=strcat('',year_list_text(i,:),'.',quarter_list_text(j,:),'');
%         xlswrite(filename, xx, sheetlist);
%         clear xx sheetlist
%     end
% end







